﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using System.Collections;
using Agresso.Interface.Layer.Domain;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using System.Data;

namespace Agresso.Interface.Layer.Service
{
    public class CrewPayrollService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        agldimvalueService m_agldimvalueService;
        AcrBatcInputService m_AcrBatcInputService;
        AGRESO_SYNCService m_AGRESO_SYNCService;
        ConfigurationSettingService m_configSettingService;


        public CrewPayrollService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
            m_agldimvalueService = new agldimvalueService();
            m_AGRESO_SYNCService = new AGRESO_SYNCService();
            m_AcrBatcInputService = new AcrBatcInputService();
            m_configSettingService = new ConfigurationSettingService();
        }

        public void ExecuteCrewPayrollInterface()
        {
            try 
            {
                IList pendingList = m_AGRESO_SYNCService.GetAllPending();//GetAllPendingFromMiddleLayer();// GetAllPending();
                int time = 0;// Convert.ToInt16(DateTime.Now.ToString("mm"));
                string prevCLient = "";
                string prevvessel = "";
                int voucherNo = GetSequenceRunningNumber();
                //int count = 0;
                IList acr_S1_3101 = new ArrayList();
                IList acr_M1_3101 = new ArrayList();
                IList acr_others = new ArrayList();
                IList acr_all = new ArrayList();
               // DateTime trans_date = new DateTime(2013, 10, 31);

                foreach (AGRESO_SYNC agr in pendingList)
                {
                    //count++;
                    //if (count == 292)
                    //{
 
                    //}
                    string alert = string.Empty;
                    if (agr.AS_ACCOUNT == "")
                        alert += "Account is empty" + " \r\n";
                    if (agr.AS_COMPANY_CODE == "")
                        alert += "AS_COMPANY_CODE is empty" + " \r\n";
                    agldimvalue legalEntity = m_agldimvalueService.GetAglDimValueProfitCentreByDimValueForCrewPayroll(agr.AS_COMPANY_CODE);
                    agldimvalue vessel = null;
                    agldimvalue interLegalEntity = null;
                    agr.AS_VESSEL_ACC_CODE = agr.AS_VESSEL_ACC_CODE.Replace(" ","");

                    if (legalEntity == null)
                        alert += "LE is not found in Agresso : " + agr.AS_COMPANY_CODE + " \r\n";
                    else
                        if (legalEntity.client == "")
                            alert += "Client is empty, LE : " + agr.AS_COMPANY_CODE + " \r\n";
                        else
                        {
                            if (legalEntity.client == "S1")
                                vessel = m_agldimvalueService.GetAglDimValueVesselByDimValue(agr.AS_VESSEL_ACC_CODE);
                            else
                                vessel = m_agldimvalueService.GetAglDimValueVesselByDimValueAndClient(agr.AS_VESSEL_ACC_CODE, legalEntity.client);
                        }

                    if (vessel == null)
                        alert += "Vessel is not found in Agresso : " + agr.AS_VESSEL_ACC_CODE + " \r\n";
                    else
                    {
                        if (vessel.rel_value == "")
                            alert += "Cost Centre is empty, vessel : " + agr.AS_VESSEL_ACC_CODE + " \r\n";
                        interLegalEntity = m_agldimvalueService.GetAglDimValueLegalEntityByCostCentreDimVal(vessel.rel_value);
                    }
                    if (interLegalEntity == null)
                        alert += "Inter LE for Vessel is not found in Agresso : " + agr.AS_VESSEL_ACC_CODE + " \r\n";
                   
                    if (alert.Trim().Length > 0)
                    {
                        UpdateRemarksDetail(alert, 0, "", agr);
                        continue;
                    }

                    if((prevCLient != "") || (prevvessel != ""))
                        if ((prevCLient != legalEntity.client) || (prevvessel != agr.AS_VESSEL_ACC_CODE))
                            time++;
                    prevCLient = legalEntity.client;
                    prevvessel = agr.AS_VESSEL_ACC_CODE;
                    acrbatchinput acr = new acrbatchinput();
                    acr.batch_id = "CMS" + agr.AS_COMPANY_CODE + DateTime.Now.ToString("yyyyMMddHH") + time.ToString().PadLeft(2, '0');
                    //acr.interface_ = "BI";
                    //acr.voucher_type = "GL";
                    //acr.trans_type = "GL";
                    acr.client = legalEntity.client;
                    acr.status = "N";
                    acr.trans_date = DateTime.Today;//new DateTime(agr.AS_YEAR, agr.AS_MONTH, DateTime.DaysInMonth(agr.AS_YEAR, agr.AS_MONTH));  //trans_date; // DateTime.Today;
                    acr.voucher_date = acr.trans_date;// trans_date;// new DateTime(agr.AS_ADD_DATE.Year, agr.AS_ADD_DATE.Month, DateTime.DaysInMonth(agr.AS_ADD_DATE.Year, agr.AS_ADD_DATE.Month));//DateTime.Today;
                    acr.voucher_no = voucherNo;
                    acr.period = Convert.ToInt32(agr.AS_ADD_DATE.Year.ToString() + agr.AS_ADD_DATE.Month.ToString().PadLeft(2, '0'));// follow add date , Jasper 17022014
                    //Convert.ToInt32(trans_date.ToString("yyyyMM")); //
                    acr.dim_5 = agr.AS_VESSEL_ACC_CODE;
                    acr.dim_6 = agr.AS_COMPANY_CODE;
                    acr.currency = agr.AS_CURRENCY;
                    acr.cur_amount = agr.AS_AMOUNT;
                    //acr.description = agr.AS_DESCRIPTION + "," + acr.period.ToString(); //  ", 201310";//agr.AS_VESSEL_NAME + ", " +
                    int period = Convert.ToInt32(agr.AS_YEAR.ToString() + agr.AS_MONTH.ToString().PadLeft(2, '0'));
                    //acr.period = period;
                    acr.description = agr.AS_DESCRIPTION + "," + period.ToString(); //follow crewpayroll period. Jasper 17022014
                    acr.sequence_no = GetSequenceRunningNumber();

                    if(acr.dim_6.Trim() == "603" || acr.dim_6.Trim() == "502") // Jasper 23 Jan 2014
                    {
                        acr.trans_type = "GL";
                        acr.voucher_type = "CP";
                        acr.interface_ = "CP";
                    }
                    else if (acr.dim_6.Trim() == "401" || acr.dim_6.Trim() == "203")
                    {
                        acr.trans_type = "GL";
                        acr.voucher_type = "GL";
                        acr.interface_ = "BI";
                    }
                    else
                    {
                        acr.trans_type = "GL";
                        acr.voucher_type = "CP";
                        acr.interface_ = "CP";
                    }

                    if (legalEntity.client.Trim() == "S1" || legalEntity.client.Trim() == "M1")
                    {
                        acr.dim_1 = legalEntity.rel_value;
                        if ((Convert.ToInt32(agr.AS_ACCOUNT)) < 7000 && (agr.AS_ACCOUNT != "3305"))
                        {
                            acr.account = agr.AS_ACCOUNT;
                        }
                        else
                        {
                            acr.account = "2904";
                            acr.dim_2 = agr.AS_ACCOUNT;
                            acr.dim_7 = interLegalEntity.dim_value;
                        }
                    }
                    else 
                    {
                        acr.account = agr.AS_ACCOUNT;
                        acr.dim_1 = vessel.rel_value;
                    }
                    acr.Tag = agr;

                    if (acr.client.Trim() == "S1" && acr.account.Trim() == "3101")
                    {
                        //acr.description = agr.AS_VESSEL_NAME + ", " + acr.period;
                        acr_S1_3101.Add(acr);
                    }
                    else if (acr.client.Trim() == "M1" && acr.account.Trim() == "3101")
                    {
                        //acr.description = agr.AS_VESSEL_NAME + ", " + acr.period;
                        acr_M1_3101.Add(acr);
                    }
                    else
                        acr_others.Add(acr);

                    acr_all.Add(acr); // for update the status...

                    //m_AcrBatcInputService.Insert(acr);
                    //m_AcrBatcInputService.InsertToInterfaceTable(acr);
                    //m_AGRESO_SYNCService.UpdateStatus(agr.AS_ID, 1);//, DateTime.Now);
                    //UpdateRemarksDetail(agr.AS_ID, alert, 1, acr.batch_id, agr.AS_ADD_DATE);
                    
                }

                IList s1_3101_dim = new ArrayList();
                foreach (acrbatchinput a in acr_S1_3101)
                {
                    if (s1_3101_dim.Count == 0)
                    { 
                        s1_3101_dim.Add(a.Duplicate()); 
                        continue; 
                    }
                    if (!s1_3101_dim.Contains(a))
                        s1_3101_dim.Add(a.Duplicate());
                }
                foreach (acrbatchinput a in s1_3101_dim)
                {
                    a.cur_amount = 0;
                    foreach (acrbatchinput b in acr_S1_3101)
                    {
                        if (a.Equals(b))
                            a.cur_amount += b.cur_amount;
                    }
                }
                foreach (acrbatchinput a in s1_3101_dim)
                    acr_others.Add(a);

                IList m1_3101_dim = new ArrayList();
                foreach (acrbatchinput a in acr_M1_3101)
                {
                    if (m1_3101_dim.Count == 0)
                    {
                        m1_3101_dim.Add(a.Duplicate());
                        continue;
                    }
                    if (!m1_3101_dim.Contains(a))
                        m1_3101_dim.Add(a.Duplicate());
                }
                foreach (acrbatchinput a in m1_3101_dim)
                {
                    a.cur_amount = 0;
                    foreach (acrbatchinput b in acr_M1_3101)
                    {
                        if (a.Equals(b))
                            a.cur_amount += b.cur_amount;
                    }
                }
                foreach (acrbatchinput a in m1_3101_dim)
                    acr_others.Add(a);

                foreach (acrbatchinput acr in acr_others)//Inser ACRbatch
                {
                   m_AcrBatcInputService.Insert(acr);
                    m_AcrBatcInputService.InsertToInterfaceTable(acr);
                }
                foreach (acrbatchinput acr in acr_all)//update status----
                {
                   AGRESO_SYNC agr = (AGRESO_SYNC)acr.Tag;
                    m_AGRESO_SYNCService.UpdateStatus(agr.AS_ID, 1);//, DateTime.Now);
                    UpdateRemarksDetail("", 1, acr.batch_id, agr);
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
            }
        }
        public void ExecuteCrewPayrollInterface(int month, int year, bool exported, string[] vesselname, int period, int textPeriod, DateTime transdate, int AS_COMPANY_CODE)
        {
            try
            {
                IList pendingList = m_AGRESO_SYNCService.GetAllPending(month, year, vesselname, exported, AS_COMPANY_CODE);
                int time = 0;// Convert.ToInt16(DateTime.Now.ToString("mm"));
                string prevCLient = "";
                string prevvessel = "";
                int voucherNo = GetSequenceRunningNumber();
                //int count = 0;
                IList acr_S1_3101 = new ArrayList();
                IList acr_M1_3101 = new ArrayList();
                IList acr_others = new ArrayList();
                IList acr_all = new ArrayList();
                // DateTime trans_date = new DateTime(2013, 10, 31);

                foreach (AGRESO_SYNC agr in pendingList)
                {
                    //count++;
                    //if (count == 292)
                    //{

                    //}
                    string alert = string.Empty;
                    if (agr.AS_ACCOUNT == "")
                        alert += "Account is empty" + " \r\n";
                    if (agr.AS_COMPANY_CODE == "")
                        alert += "AS_COMPANY_CODE is empty" + " \r\n";
                    agldimvalue legalEntity = m_agldimvalueService.GetAglDimValueProfitCentreByDimValueForCrewPayroll(agr.AS_COMPANY_CODE);
                    agldimvalue vessel = null;
                    agldimvalue interLegalEntity = null;
                    agr.AS_VESSEL_ACC_CODE = agr.AS_VESSEL_ACC_CODE.Replace(" ", "");

                    if (legalEntity == null)
                        alert += "LE is not found in Agresso : " + agr.AS_COMPANY_CODE + " \r\n";
                    else
                        if (legalEntity.client == "")
                            alert += "Client is empty, LE : " + agr.AS_COMPANY_CODE + " \r\n";
                        else
                        {
                            if (legalEntity.client == "S1")
                                vessel = m_agldimvalueService.GetAglDimValueVesselByDimValue(agr.AS_VESSEL_ACC_CODE);
                            else
                                vessel = m_agldimvalueService.GetAglDimValueVesselByDimValueAndClient(agr.AS_VESSEL_ACC_CODE, legalEntity.client);
                        }

                    if (vessel == null)
                        alert += "Vessel is not found in Agresso : " + agr.AS_VESSEL_ACC_CODE + " \r\n";
                    else
                    {
                        if (vessel.rel_value == "")
                            alert += "Cost Centre is empty, vessel : " + agr.AS_VESSEL_ACC_CODE + " \r\n";
                        interLegalEntity = m_agldimvalueService.GetAglDimValueLegalEntityByCostCentreDimVal(vessel.rel_value);
                    }
                    if (interLegalEntity == null)
                        alert += "Inter LE for Vessel is not found in Agresso : " + agr.AS_VESSEL_ACC_CODE + " \r\n";

                    if (alert.Trim().Length > 0)
                    {
                        UpdateRemarksDetail(alert, 0, "", agr);
                        continue;
                    }

                    if ((prevCLient != "") || (prevvessel != ""))
                        if ((prevCLient != legalEntity.client) || (prevvessel != agr.AS_VESSEL_ACC_CODE))
                            time++;
                    prevCLient = legalEntity.client;
                    prevvessel = agr.AS_VESSEL_ACC_CODE;
                    acrbatchinput acr = new acrbatchinput();
                    acr.batch_id = "CMS" + agr.AS_COMPANY_CODE + DateTime.Now.ToString("yyyyMMddHH") + time.ToString().PadLeft(2, '0');
                    //acr.interface_ = "BI";
                    //acr.voucher_type = "GL";
                    //acr.trans_type = "GL";
                    acr.client = legalEntity.client;
                    acr.status = "N";
                    acr.trans_date = transdate;//new DateTime(agr.AS_YEAR, agr.AS_MONTH, DateTime.DaysInMonth(agr.AS_YEAR, agr.AS_MONTH));  //trans_date; // DateTime.Today;
                    acr.voucher_date = acr.trans_date;// trans_date;// new DateTime(agr.AS_ADD_DATE.Year, agr.AS_ADD_DATE.Month, DateTime.DaysInMonth(agr.AS_ADD_DATE.Year, agr.AS_ADD_DATE.Month));//DateTime.Today;
                    acr.voucher_no = voucherNo;
                    acr.period = period;
                    //Convert.ToInt32(trans_date.ToString("yyyyMM")); //
                    acr.dim_5 = agr.AS_VESSEL_ACC_CODE;
                    acr.dim_6 = agr.AS_COMPANY_CODE;
                    acr.currency = agr.AS_CURRENCY;
                    acr.cur_amount = agr.AS_AMOUNT;
                    //acr.description = agr.AS_DESCRIPTION + "," + acr.period.ToString(); //  ", 201310";//agr.AS_VESSEL_NAME + ", " +
                    //int period = Convert.ToInt32(agr.AS_YEAR.ToString() + agr.AS_MONTH.ToString().PadLeft(2, '0'));
                    //acr.period = period;
                    acr.description = agr.AS_DESCRIPTION + "," + textPeriod.ToString(); //follow crewpayroll period. Jasper 17022014
                    acr.sequence_no = GetSequenceRunningNumber();

                    if (acr.dim_6.Trim() == "603" || acr.dim_6.Trim() == "502") // Jasper 23 Jan 2014
                    {
                        acr.trans_type = "GL";
                        acr.voucher_type = "CP";
                        acr.interface_ = "CP";
                    }
                    else if (acr.dim_6.Trim() == "401" || acr.dim_6.Trim() == "203")
                    {
                        acr.trans_type = "GL";
                        acr.voucher_type = "GL";
                        acr.interface_ = "BI";
                    }
                    else
                    {
                        acr.trans_type = "GL";
                        acr.voucher_type = "CP";
                        acr.interface_ = "CP";
                    }

                    if (legalEntity.client.Trim() == "S1" || legalEntity.client.Trim() == "M1")
                    {
                        acr.dim_1 = legalEntity.rel_value;
                        if ((Convert.ToInt32(agr.AS_ACCOUNT)) < 7000 && (agr.AS_ACCOUNT != "3305"))
                        {
                            acr.account = agr.AS_ACCOUNT;
                        }
                        else
                        {
                            acr.account = "2904";
                            acr.dim_2 = agr.AS_ACCOUNT;
                            acr.dim_7 = interLegalEntity.dim_value;
                        }
                    }
                    else
                    {
                        acr.account = agr.AS_ACCOUNT;
                        acr.dim_1 = vessel.rel_value;
                    }
                    acr.Tag = agr;

                    if (acr.client.Trim() == "S1" && acr.account.Trim() == "3101")
                    {
                        //acr.description = agr.AS_VESSEL_NAME + ", " + acr.period;
                        acr_S1_3101.Add(acr);
                    }
                    else if (acr.client.Trim() == "M1" && acr.account.Trim() == "3101")
                    {
                        //acr.description = agr.AS_VESSEL_NAME + ", " + acr.period;
                        acr_M1_3101.Add(acr);
                    }
                    else
                        acr_others.Add(acr);

                    acr_all.Add(acr); // for update the status...

                    //m_AcrBatcInputService.Insert(acr);
                    //m_AcrBatcInputService.InsertToInterfaceTable(acr);
                    //m_AGRESO_SYNCService.UpdateStatus(agr.AS_ID, 1);//, DateTime.Now);
                    //UpdateRemarksDetail(agr.AS_ID, alert, 1, acr.batch_id, agr.AS_ADD_DATE);

                }

                IList s1_3101_dim = new ArrayList();
                foreach (acrbatchinput a in acr_S1_3101)
                {
                    if (s1_3101_dim.Count == 0)
                    {
                        s1_3101_dim.Add(a.Duplicate());
                        continue;
                    }
                    if (!s1_3101_dim.Contains(a))
                        s1_3101_dim.Add(a.Duplicate());
                }
                foreach (acrbatchinput a in s1_3101_dim)
                {
                    a.cur_amount = 0;
                    foreach (acrbatchinput b in acr_S1_3101)
                    {
                        if (a.Equals(b))
                            a.cur_amount += b.cur_amount;
                    }
                }
                foreach (acrbatchinput a in s1_3101_dim)
                    acr_others.Add(a);

                IList m1_3101_dim = new ArrayList();
                foreach (acrbatchinput a in acr_M1_3101)
                {
                    if (m1_3101_dim.Count == 0)
                    {
                        m1_3101_dim.Add(a.Duplicate());
                        continue;
                    }
                    if (!m1_3101_dim.Contains(a))
                        m1_3101_dim.Add(a.Duplicate());
                }
                foreach (acrbatchinput a in m1_3101_dim)
                {
                    a.cur_amount = 0;
                    foreach (acrbatchinput b in acr_M1_3101)
                    {
                        if (a.Equals(b))
                            a.cur_amount += b.cur_amount;
                    }
                }
                foreach (acrbatchinput a in m1_3101_dim)
                    acr_others.Add(a);

                foreach (acrbatchinput acr in acr_others)//Inser ACRbatch
                {
                    m_AcrBatcInputService.Insert(acr);
                    m_AcrBatcInputService.InsertToInterfaceTable(acr);
                }
                foreach (acrbatchinput acr in acr_all)//update status----
                {
                    AGRESO_SYNC agr = (AGRESO_SYNC)acr.Tag;
                    m_AGRESO_SYNCService.UpdateStatus(agr.AS_ID, 1);//, DateTime.Now);
                    UpdateRemarksDetail("", 1, acr.batch_id, agr);
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
            }
        }
        public void ExecuteCrewPayrollStagingInterface()
        {
            try
            {
                IList pendingList = m_AGRESO_SYNCService.GetAllPendingStaging();
                int time = Convert.ToInt16(DateTime.Now.ToString("mm"));
                string prevCLient = "";
                int voucherNo = GetSequenceRunningNumber();
                IList acr_S1_3101 = new ArrayList();
                IList acr_M1_3101 = new ArrayList();
                IList acr_others = new ArrayList();
                IList acr_all = new ArrayList();

                foreach (AGRESO_SYNC agr in pendingList)
                {
                    string alert = string.Empty;
                    if (agr.AS_ACCOUNT == "")
                        alert += "Account is empty" + " \r\n";
                    if (agr.AS_COMPANY_CODE == "")
                        alert += "AS_COMPANY_CODE is empty" + " \r\n";
                    agldimvalue legalEntity = m_agldimvalueService.GetAglDimValueProfitCentreByDimValueForCrewPayroll(agr.AS_COMPANY_CODE);
                    agldimvalue vessel = null;
                    agldimvalue interLegalEntity = null;
                    agr.AS_VESSEL_ACC_CODE = agr.AS_VESSEL_ACC_CODE.Replace(" ", "");

                    if (legalEntity == null)
                        alert += "LE is not found in Agresso : " + agr.AS_COMPANY_CODE + " \r\n";
                    else
                        if (legalEntity.client == "")
                            alert += "Client is empty, LE : " + agr.AS_COMPANY_CODE + " \r\n";
                        else
                        {
                            if (legalEntity.client == "S1")
                                vessel = m_agldimvalueService.GetAglDimValueVesselByDimValue(agr.AS_VESSEL_ACC_CODE);
                            else
                                vessel = m_agldimvalueService.GetAglDimValueVesselByDimValueAndClient(agr.AS_VESSEL_ACC_CODE, legalEntity.client);
                        }

                    if (vessel == null)
                        alert += "Vessel is not found in Agresso : " + agr.AS_VESSEL_ACC_CODE + " \r\n";
                    else
                    {
                        if (vessel.rel_value == "")
                            alert += "Cost Centre is empty, vessel : " + agr.AS_VESSEL_ACC_CODE + " \r\n";
                        interLegalEntity = m_agldimvalueService.GetAglDimValueLegalEntityByCostCentreDimVal(vessel.rel_value);
                    }
                    if (interLegalEntity == null)
                        alert += "Inter LE for Vessel is not found in Agresso : " + agr.AS_VESSEL_ACC_CODE + " \r\n";

                    if (alert.Trim().Length > 0)
                    {
                        UpdateRemarksDetail(alert, 0, "", agr);
                        continue;
                    }
                    if ((prevCLient != ""))
                        if ((prevCLient != legalEntity.client))
                            time++;
                    prevCLient = legalEntity.client;
                    acrbatchinput acr = new acrbatchinput();
                    acr.batch_id = "CMS" + agr.AS_COMPANY_CODE + DateTime.Now.ToString("yyyyMMddHH") + time.ToString().PadLeft(2, '0');
                    acr.client = legalEntity.client;
                    acr.status = "N";
                    acr.trans_date = DateTime.Today;//new DateTime(agr.AS_YEAR, agr.AS_MONTH, DateTime.DaysInMonth(agr.AS_YEAR, agr.AS_MONTH));  //trans_date; // DateTime.Today;
                    acr.voucher_date = acr.trans_date;// trans_date;// new DateTime(agr.AS_ADD_DATE.Year, agr.AS_ADD_DATE.Month, DateTime.DaysInMonth(agr.AS_ADD_DATE.Year, agr.AS_ADD_DATE.Month));//DateTime.Today;
                    acr.voucher_no = voucherNo;
                    acr.period = 201401;// Convert.ToInt32(agr.AS_YEAR.ToString() + agr.AS_MONTH.ToString().PadLeft(2, '0'));//Convert.ToInt32(trans_date.ToString("yyyyMM")); //
                    acr.dim_5 = agr.AS_VESSEL_ACC_CODE;
                    acr.dim_6 = agr.AS_COMPANY_CODE;
                    acr.currency = agr.AS_CURRENCY;
                    acr.cur_amount = agr.AS_AMOUNT;
                    acr.description = agr.AS_DESCRIPTION + ",201311";// +acr.period.ToString(); //  ", 201310";//agr.AS_VESSEL_NAME + ", " +
                    acr.sequence_no = GetSequenceRunningNumber();

                    if (acr.dim_6.Trim() == "603" || acr.dim_6.Trim() == "502") // Jasper 23 Jan 2014
                    {
                        acr.trans_type = "GL";
                        acr.voucher_type = "CP";
                        acr.interface_ = "CP";
                    }
                    else if (acr.dim_6.Trim() == "401" || acr.dim_6.Trim() == "203")
                    {
                        acr.trans_type = "GL";
                        acr.voucher_type = "GL";
                        acr.interface_ = "BI";
                    }
                    else
                    {
                        acr.trans_type = "GL";
                        acr.voucher_type = "CP";
                        acr.interface_ = "CP";
                    }

                    if (legalEntity.client.Trim() == "S1" || legalEntity.client.Trim() == "M1")
                    {
                        acr.dim_1 = legalEntity.rel_value;
                        if (Convert.ToInt32(agr.AS_ACCOUNT) < 7000)
                        {
                            acr.account = agr.AS_ACCOUNT;
                        }
                        else
                        {
                            acr.account = "2904";
                            acr.dim_2 = agr.AS_ACCOUNT;
                            acr.dim_7 = interLegalEntity.dim_value;
                        }
                    }
                    else
                    {
                        acr.account = agr.AS_ACCOUNT;
                        acr.dim_1 = vessel.rel_value;
                    }
                    acr.Tag = agr;

                    if (acr.client.Trim() == "S1" && acr.account.Trim() == "3101")
                    {
                        //acr.description = agr.AS_VESSEL_NAME + ", " + acr.period;
                        acr_S1_3101.Add(acr);
                    }
                    else if (acr.client.Trim() == "M1" && acr.account.Trim() == "3101")
                    {
                        acr_M1_3101.Add(acr);
                    }
                    else
                        acr_others.Add(acr);

                    acr_all.Add(acr); 
                }

                IList s1_3101_dim = new ArrayList();
                foreach (acrbatchinput a in acr_S1_3101)
                {
                    if (s1_3101_dim.Count == 0)
                    {
                        s1_3101_dim.Add(a.Duplicate());
                        continue;
                    }
                    if (!s1_3101_dim.Contains(a))
                        s1_3101_dim.Add(a.Duplicate());
                }
                foreach (acrbatchinput a in s1_3101_dim)
                {
                    a.cur_amount = 0;
                    foreach (acrbatchinput b in acr_S1_3101)
                    {
                        if (a.Equals(b))
                            a.cur_amount += b.cur_amount;
                    }
                }
                foreach (acrbatchinput a in s1_3101_dim)
                    acr_others.Add(a);

                IList m1_3101_dim = new ArrayList();
                foreach (acrbatchinput a in acr_M1_3101)
                {
                    if (m1_3101_dim.Count == 0)
                    {
                        m1_3101_dim.Add(a.Duplicate());
                        continue;
                    }
                    if (!m1_3101_dim.Contains(a))
                        m1_3101_dim.Add(a.Duplicate());
                }
                foreach (acrbatchinput a in m1_3101_dim)
                {
                    a.cur_amount = 0;
                    foreach (acrbatchinput b in acr_M1_3101)
                    {
                        if (a.Equals(b))
                            a.cur_amount += b.cur_amount;
                    }
                }
                foreach (acrbatchinput a in m1_3101_dim)
                    acr_others.Add(a);

                foreach (acrbatchinput acr in acr_others)//Inser ACRbatch
                {
                    m_AcrBatcInputService.Insert(acr);
                    m_AcrBatcInputService.InsertToInterfaceTable(acr);
                }
                foreach (acrbatchinput acr in acr_all)//update status----
                {
                    AGRESO_SYNC agr = (AGRESO_SYNC)acr.Tag;
                    m_AGRESO_SYNCService.UpdateStagingStatus(agr.AS_ID, 1);//, DateTime.Now);
                    UpdateRemarksDetail("", 1, acr.batch_id, agr);
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
            }
        }
        private int GetSequenceRunningNumber()
        {
            ConfigurationSetting runningNumber = m_configSettingService.GetConfigurationSetting("RunningNumber");
            int number = Convert.ToInt32(runningNumber.val_2) + 1;
            runningNumber.val_2 = number.ToString();
            m_configSettingService.UpdateConfiguration(runningNumber);
            return number;
        }
        private void UpdateRemarksDetail(string remarks, int pickupflag, string batch_id, AGRESO_SYNC agrs)
        {           
            m_dataBaseConectivity.InterfaceSqlConnection_c.Open();
            i_AGRESO_SYNC agr = null;
            SqlCommand cmd = new SqlCommand(CrewPayrollSQL.FindByAS_IDAndADDDate(), m_dataBaseConectivity.InterfaceSqlConnection_c);
            cmd.Parameters.Add("@AS_ID", SqlDbType.BigInt).Value = agrs.AS_ID;
            cmd.Parameters.Add("@AS_ADD_DATE", SqlDbType.DateTime).Value = agrs.AS_ADD_DATE;
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
                agr = new i_AGRESO_SYNC(rd);
            rd.Close();
            if (agr == null)
            {
                cmd.CommandText = CrewPayrollSQL.InsertTempTable();
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@AS_ID", SqlDbType.BigInt).Value = agrs.AS_ID;
                cmd.Parameters.Add("@AS_PICKUP_DATE_TIME", SqlDbType.NVarChar).Value = DateTime.Now.ToString("yyyyMMdd HH:mm");
                cmd.Parameters.Add("@AS_IS_EXPORTED", SqlDbType.Int).Value = pickupflag;
                cmd.Parameters.Add("@remarks", SqlDbType.NVarChar).Value = remarks;
                cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@batch_id", SqlDbType.NVarChar).Value = batch_id;

                cmd.Parameters.Add("@AS_ADD_DATE", SqlDbType.DateTime).Value = agrs.AS_ADD_DATE;
                cmd.Parameters.Add("@AS_ACCOUNT", SqlDbType.VarChar).Value = agrs.AS_ACCOUNT;
                cmd.Parameters.Add("@AS_VESSEL_ACC_CODE", SqlDbType.VarChar).Value = agrs.AS_VESSEL_ACC_CODE;
                cmd.Parameters.Add("@AS_VESSEL_CODE", SqlDbType.VarChar).Value = agrs.AS_VESSEL_CODE;
                cmd.Parameters.Add("@AS_MONTH", SqlDbType.Int).Value = agrs.AS_MONTH;
                cmd.Parameters.Add("@AS_YEAR", SqlDbType.Int).Value = agrs.AS_YEAR;
                cmd.Parameters.Add("@AS_COMPANY_CODE", SqlDbType.VarChar).Value = agrs.AS_COMPANY_CODE;
                cmd.Parameters.Add("@AS_CURRENCY", SqlDbType.VarChar).Value = agrs.AS_CURRENCY;
                cmd.Parameters.Add("@AS_AMOUNT", SqlDbType.Money).Value = agrs.AS_AMOUNT;
                cmd.Parameters.Add("@AS_DESCRIPTION", SqlDbType.VarChar).Value = agrs.AS_DESCRIPTION;
                cmd.Parameters.Add("@AS_ADD_BY", SqlDbType.Int).Value = agrs.AS_ADD_BY;
                cmd.Parameters.Add("@AS_VESSEL_NAME", SqlDbType.VarChar).Value = agrs.AS_VESSEL_NAME;
                cmd.Parameters.Add("@AS_COMPANY_NAME", SqlDbType.VarChar).Value = agrs.AS_COMPANY_NAME;
                cmd.Parameters.Add("@pickup_date", SqlDbType.DateTime).Value = DateTime.Now;
                cmd.ExecuteNonQuery();
            }
            else
            {
                if ((remarks != agr.remarks) || agr.batch_id != batch_id)
                {
                    cmd.CommandText = CrewPayrollSQL.UpdateTempPickup_flagStatus();
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@AS_ID", SqlDbType.BigInt).Value = agrs.AS_ID;
                    cmd.Parameters.Add("@AS_ADD_DATE", SqlDbType.DateTime).Value = agrs.AS_ADD_DATE;
                    cmd.Parameters.Add("@AS_IS_EXPORTED", SqlDbType.Int).Value = pickupflag;
                    cmd.Parameters.Add("@AS_PICKUP_DATE_TIME", SqlDbType.NVarChar).Value = DateTime.Now.ToString("yyyyMMdd HH:mm");
                    cmd.Parameters.Add("@remarks", SqlDbType.NVarChar).Value = remarks;
                    cmd.Parameters.Add("@batch_id", SqlDbType.NVarChar).Value = batch_id;
                    cmd.Parameters.Add("@pickup_date", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.ExecuteNonQuery();
                }
            }
            m_dataBaseConectivity.InterfaceSqlConnection_c.Close();
        }

        public IList GetAllPendingCrewPayroll(string p)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = CrewPayrollSQL.GetAllPendingCrewPayroll(p);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new i_AGRESO_SYNC(rd));
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public IList GetAllPendingCrewPayroll()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = CrewPayrollSQL.GetAllPendingCrewPayroll();
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new i_AGRESO_SYNC(rd));
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        //public void updateNotExported() // 11022014 update
        //{
        //    m_dataBaseConectivity.InterfaceSqlConnection.Open();
        //    m_dataBaseConectivity.CrewPayrollSqlConnection.Open();
        //    SqlCommand cmd = new SqlCommand();
        //    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
        //    cmd.CommandText = "select distinct AS_ID from i_AGRESO_SYNC where insert_date > '2014-02-11'";
        //    SqlDataReader rd = cmd.ExecuteReader();
        //    SqlCommand cmdCP = new SqlCommand();
        //    cmdCP.Connection = m_dataBaseConectivity.CrewPayrollSqlConnection;
        //    cmdCP.CommandText = "Update AGRESO_SYNC set AS_IS_EXPORTED = null where AS_ID=@id";
        //    while (rd.Read())
        //    {
        //        int id = Convert.ToInt32(rd["AS_ID"]);
        //        cmdCP.Parameters.Clear();
        //        cmdCP.Parameters.Add("@id", SqlDbType.Int).Value = id;
        //        cmdCP.ExecuteNonQuery();
        //    }

        //    m_dataBaseConectivity.InterfaceSqlConnection.Close();
        //    m_dataBaseConectivity.CrewPayrollSqlConnection.Close();

        //}
    }
}
